#!/usr/bin/env python3
# -*- coding: utf-8 -*-
from sqlalchemy import func
from app import db
from app.models.dictionary import DictCategory, DictItem


class DictionaryService:
    """字典管理服务"""
    
    @staticmethod
    def get_categories(page=1, per_page=10, keyword=''):
        """获取字典分类列表"""
        query = DictCategory.query
        
        # 关键字搜索
        if keyword:
            query = query.filter(
                db.or_(
                    DictCategory.dict_name.like(f'%{keyword}%'),
                    DictCategory.dict_code.like(f'%{keyword}%'),
                    DictCategory.remark.like(f'%{keyword}%')
                )
            )
        
        # 按排序字段和ID排序
        query = query.order_by(DictCategory.sort_order.asc(), DictCategory.id.asc())
        
        # 分页
        total = query.count()
        categories = query.offset((page - 1) * per_page).limit(per_page).all()
        
        return {
            'list': [category.to_dict() for category in categories],
            'pagination': {
                'page': page,
                'per_page': per_page,
                'total': total,
                'pages': (total + per_page - 1) // per_page
            }
        }
    
    @staticmethod
    def get_category_by_id(category_id):
        """根据ID获取字典分类"""
        return DictCategory.query.get(category_id)
    
    @staticmethod
    def get_category_by_code(dict_code):
        """根据编码获取字典分类"""
        return DictCategory.query.filter_by(dict_code=dict_code).first()
    
    @staticmethod
    def create_category(data):
        """创建字典分类"""
        # 检查编码是否已存在
        existing = DictCategory.query.filter_by(dict_code=data['dict_code']).first()
        if existing:
            raise ValueError(f"字典编码 '{data['dict_code']}' 已存在")
        
        category = DictCategory(
            dict_name=data['dict_name'],
            dict_code=data['dict_code'],
            sort_order=data.get('sort_order', 0),
            remark=data.get('remark', ''),
            is_active=data.get('is_active', True)
        )
        
        db.session.add(category)
        db.session.commit()
        return category
    
    @staticmethod
    def update_category(category_id, data):
        """更新字典分类"""
        category = DictCategory.query.get(category_id)
        if not category:
            raise ValueError("字典分类不存在")
        
        # 检查编码是否被其他分类使用
        if 'dict_code' in data and data['dict_code'] != category.dict_code:
            existing = DictCategory.query.filter(
                DictCategory.dict_code == data['dict_code'],
                DictCategory.id != category_id
            ).first()
            if existing:
                raise ValueError(f"字典编码 '{data['dict_code']}' 已存在")
        
        # 更新字段
        for key, value in data.items():
            if hasattr(category, key):
                setattr(category, key, value)
        
        db.session.commit()
        return category
    
    @staticmethod
    def delete_category(category_id):
        """删除字典分类"""
        category = DictCategory.query.get(category_id)
        if not category:
            raise ValueError("字典分类不存在")
        
        # 检查是否存在关联的字典项
        item_count = category.dict_items.count()
        if item_count > 0:
            raise ValueError(f"该字典分类下还有 {item_count} 个字典项，无法删除")
        
        db.session.delete(category)
        db.session.commit()
        return True
    
    @staticmethod
    def get_items(dict_id=None, page=1, per_page=10, keyword=''):
        """获取字典项列表"""
        query = DictItem.query.join(DictCategory)
        
        # 按字典分类过滤
        if dict_id:
            query = query.filter(DictItem.dict_id == dict_id)
        
        # 关键字搜索
        if keyword:
            query = query.filter(
                db.or_(
                    DictItem.item_name.like(f'%{keyword}%'),
                    DictItem.item_value.like(f'%{keyword}%'),
                    DictItem.remark.like(f'%{keyword}%'),
                    DictCategory.dict_name.like(f'%{keyword}%')
                )
            )
        
        # 排序
        query = query.order_by(
            DictCategory.sort_order.asc(),
            DictItem.sort_order.asc(),
            DictItem.id.asc()
        )
        
        # 分页
        total = query.count()
        items = query.offset((page - 1) * per_page).limit(per_page).all()
        
        return {
            'list': [item.to_dict() for item in items],
            'pagination': {
                'page': page,
                'per_page': per_page,
                'total': total,
                'pages': (total + per_page - 1) // per_page
            }
        }
    
    @staticmethod
    def get_item_by_id(item_id):
        """根据ID获取字典项"""
        return DictItem.query.get(item_id)
    
    @staticmethod
    def create_item(data):
        """创建字典项"""
        # 检查字典分类是否存在
        category = DictCategory.query.get(data['dict_id'])
        if not category:
            raise ValueError("字典分类不存在")
        
        # 检查同一分类下项名称是否重复
        existing_name = DictItem.query.filter_by(
            dict_id=data['dict_id'],
            item_name=data['item_name']
        ).first()
        if existing_name:
            raise ValueError(f"项名称 '{data['item_name']}' 在该字典分类下已存在")
        
        # 检查同一分类下数据值是否重复
        existing_value = DictItem.query.filter_by(
            dict_id=data['dict_id'],
            item_value=data['item_value']
        ).first()
        if existing_value:
            raise ValueError(f"数据值 '{data['item_value']}' 在该字典分类下已存在")
        
        item = DictItem(
            dict_id=data['dict_id'],
            item_name=data['item_name'],
            item_value=data['item_value'],
            sort_order=data.get('sort_order', 0),
            is_active=data.get('is_active', True),
            remark=data.get('remark', '')
        )
        
        db.session.add(item)
        db.session.commit()
        return item
    
    @staticmethod
    def update_item(item_id, data):
        """更新字典项"""
        item = DictItem.query.get(item_id)
        if not item:
            raise ValueError("字典项不存在")
        
        # 如果更改了分类，检查新分类是否存在
        if 'dict_id' in data and data['dict_id'] != item.dict_id:
            category = DictCategory.query.get(data['dict_id'])
            if not category:
                raise ValueError("字典分类不存在")
        
        dict_id = data.get('dict_id', item.dict_id)
        
        # 检查项名称是否重复
        if 'item_name' in data and data['item_name'] != item.item_name:
            existing_name = DictItem.query.filter(
                DictItem.dict_id == dict_id,
                DictItem.item_name == data['item_name'],
                DictItem.id != item_id
            ).first()
            if existing_name:
                raise ValueError(f"项名称 '{data['item_name']}' 在该字典分类下已存在")
        
        # 检查数据值是否重复
        if 'item_value' in data and data['item_value'] != item.item_value:
            existing_value = DictItem.query.filter(
                DictItem.dict_id == dict_id,
                DictItem.item_value == data['item_value'],
                DictItem.id != item_id
            ).first()
            if existing_value:
                raise ValueError(f"数据值 '{data['item_value']}' 在该字典分类下已存在")
        
        # 更新字段
        for key, value in data.items():
            if hasattr(item, key):
                setattr(item, key, value)
        
        db.session.commit()
        return item
    
    @staticmethod
    def delete_item(item_id):
        """删除字典项"""
        item = DictItem.query.get(item_id)
        if not item:
            raise ValueError("字典项不存在")
        
        db.session.delete(item)
        db.session.commit()
        return True
    
    @staticmethod
    def get_active_categories():
        """获取所有有效的字典分类（用于下拉选择）"""
        categories = DictCategory.query.filter_by(is_active=True)\
            .order_by(DictCategory.sort_order.asc(), DictCategory.id.asc()).all()
        return [{'id': cat.id, 'dict_name': cat.dict_name, 'dict_code': cat.dict_code} 
                for cat in categories]
    
    @staticmethod
    def get_items_by_code(dict_code, active_only=True):
        """根据字典编码获取字典项列表"""
        query = DictItem.query.join(DictCategory)\
            .filter(DictCategory.dict_code == dict_code)
        
        if active_only:
            query = query.filter(
                DictCategory.is_active == True,
                DictItem.is_active == True
            )
        
        items = query.order_by(DictItem.sort_order.asc(), DictItem.id.asc()).all()
        return [item.to_dict() for item in items] 